package com.example.collegevice.mapper;


import com.example.pojo.Choice;
import org.apache.ibatis.annotations.*;

import java.util.List;
@Mapper
public interface ChoiceMapper {
     @Insert("insert into choice(name, course_id, student_id, teacher_id) " +
            "values(#{name}, #{courseId}, #{studentId}, #{teacherId})")
    void insert(Choice choice);

//    @Select("select choice.*, teacher.name as teacherName, student.name as studentName from choice " +
//            "left join teacher on choice.teacher_id = teacher.id " +
//            "left join student on choice.student_id = student.id "
//    )
    @Select("select * from choice")
    List<Choice> selectAll();

//    @Select("select choice.*, teacher.name as teacherName, student.name as studentName from choice " +
//            "left join teacher on choice.teacher_id = teacher.id " +
//            "left join student on choice.student_id = student.id " +
//            "where choice.name like concat('%', #{name}, '%')")
    @Select( "select * from choice where name like concat('%', #{name}, '%')")
    List<Choice> selectByName(String name);

    @Update( "update choice set name= #{name}, course_id= #{courseId}, student_id= #{studentId}, teacher_id= #{teacherId} "
            + " where id = #{id}")
    void updateById(Choice choice);

    @Delete("delete from choice where id = #{id}")
    void deleteById(Integer id);

    @Select("select * from choice where course_id = #{courseId} and student_id = #{studentId}")
    List<Choice> selectByCourseIdAndStudentId(@Param("courseId") Integer courseId, @Param("studentId") Integer studentId);

//    @Select("select choice.*, teacher.name as teacherName, student.name as studentName from choice " +
//            "left join teacher on choice.teacher_id = teacher.id " +
//            "left join student on choice.student_id = student.id " +
//            "where choice.name like concat('%', #{name}, '%') and student_id = #{studentId}")
    @Select("select * from choice where name like concat('%', #{name}, '%') and student_id = #{studentId}")
    List<Choice> selectByNameAndStudentId(@Param("name") String name, @Param("studentId") Integer studentId);

//    @Select("select choice.*, teacher.name as teacherName, student.name as studentName from choice " +
//            "left join teacher on choice.teacher_id = teacher.id " +
//            "left join student on choice.student_id = student.id " +
//            "where student_id = #{studentId}"
//    )
    @Select("select * from choice where student_id = #{studentId}")
    List<Choice> selectAllByStudentId(Integer studentId);

//    @Select("select choice.*, teacher.name as teacherName, student.name as studentName from choice " +
//            "left join teacher on choice.teacher_id = teacher.id " +
//            "left join student on choice.student_id = student.id " +
//            "where choice.name like concat('%', #{name}, '%') and teacher_id = #{teacherId}")
    @Select("select * from choice where name like concat('%', #{name}, '%') and teacher_id = #{teacherId}")
    List<Choice> selectByNameAndTeacherId(@Param("name") String name, @Param("teacherId") Integer teacherId);

//    @Select("select choice.*, teacher.name as teacherName, student.name as studentName from choice " +
//            "left join teacher on choice.teacher_id = teacher.id " +
//            "left join student on choice.student_id = student.id " +
//            "where teacher_id = #{teacherId}"
//    )
     @Select("select * from choice where teacher_id = #{teacherId}")
    List<Choice> selectAllByTeacherId(Integer teacherId);

    @Select("select * from choice where id = #{id}")
    Choice selectById(Integer id);
}
